#!/bin/bash

if [ -n "$1" ] ; then
  data_date=$1
else
  data_date=`date -d "-1 days" +%F`
fi

ADS_REGION_ADS_REPORT="
INSERT OVERWRITE TABLE jtp_ads_warehouse.ads_region_ads_report
SELECT dt, province, city, is_invalid_traffic, click_count, impression_count FROM jtp_ads_warehouse.ads_region_ads_report
UNION
SELECT
    '2024-10-01' AS dt,
    if(client_province='0','未知',client_province) AS province,
    if(client_city='0','未知',client_city) AS city,
    if(is_invalid_traffic,'异常','正常') AS is_invalid_traffic,
    count(if(event_type='click',ad_id,NULL)) AS click_count,
    count(ad_id) AS impression_count
FROM jtp_ads_warehouse.dwd_ads_event_log_inc
WHERE dt='2024-10-01'
AND event_type IN ('click','impression')
GROUP BY client_province,
         client_city,
         is_invalid_traffic;
"

ADS_PLATFORM_ADS_REPORT="
INSERT OVERWRITE TABLE jtp_ads_warehouse.ads_platform_ads_report
SELECT dt, platform_id, platform_name_zh, is_invalid_traffic, click_count, impression_count FROM jtp_ads_warehouse.ads_platform_ads_report
UNION
SELECT
    '2024-10-01' AS dt,
    platform_id,
    platform_name_zh,
    if(is_invalid_traffic,'异常','正常') AS is_invalid_traffic,
    count(if(event_type='click',ad_id,NULL)) AS click_count,
    count(ad_id) AS impression_count
FROM jtp_ads_warehouse.dwd_ads_event_log_inc
WHERE dt='2024-10-01'
  AND event_type IN ('click','impression')
GROUP BY platform_id,
         platform_name_zh,
         is_invalid_traffic;
"

ADS_OS_ADS_REPORT="
INSERT OVERWRITE TABLE jtp_ads_warehouse.ads_os_ads_report
SELECT dt, client_os_type, is_invalid_traffic, click_count, impression_count FROM jtp_ads_warehouse.ads_os_ads_report
UNION
SELECT
    '2024-10-01' AS dt,
    if(client_os_type='','未知',client_os_type) AS client_os_type,
    if(is_invalid_traffic,'异常','正常') AS is_invalid_traffic,
    count(if(event_type='click',ad_id,NULL)) AS click_count,
    count(ad_id) AS impression_count
FROM jtp_ads_warehouse.dwd_ads_event_log_inc
WHERE dt='2024-10-01'
  AND event_type IN ('click','impression')
GROUP BY client_os_type,
         is_invalid_traffic;
"

ADS_HOUR_ADS_REPORT="
INSERT OVERWRITE TABLE jtp_ads_warehouse.ads_hour_ads_report
SELECT dt, ad_id, ads_name, hour_str, is_invalid_traffic, click_count, impression_count FROM jtp_ads_warehouse.ads_hour_ads_report
UNION
SELECT
    '2024-10-01' AS dt,
    ad_id,
    ads_name,
    hour(from_unixtime(event_time/1000)) AS hour_str,
    if(is_invalid_traffic,'异常','正常') AS is_invalid_traffic,
    count(if(event_time='click',ads_name,NULL)) AS click_count,
    count(ad_id) AS impression_count
FROM jtp_ads_warehouse.dwd_ads_event_log_inc
WHERE dt='2024-10-01'
  AND event_type IN ('click','impression')
GROUP BY ad_id,
         ads_name,
         hour(from_unixtime(event_time/1000)),
         is_invalid_traffic;
"

/opt/module/spark/bin/beeline -u jdbc:hive2://node101:10001 -n bwie -e "${ADS_REGION_ADS_REPORT}${ADS_PLATFORM_ADS_REPORT}
${ADS_OS_ADS_REPORT}${ADS_HOUR_ADS_REPORT}"